package resources.dao;

import java.util.ArrayList;
import java.util.Date;


import resources.pojo.pe_deptmt;
import resources.pojo.pe_eplye;
import resources.pojo.pe_eplye_detl;
import resources.util.Tool;

public class EmployeeDAO extends DatabaseDAO{
	
	public static ArrayList<pe_eplye> getListEmployee(String keyword,int idDepartment,String name,String code){
		ArrayList<pe_eplye> listTemp = new ArrayList<pe_eplye>();
		String sql = "call sp_pe_employee_select(?,?,?,?)";
		try {
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,keyword);
			cs.setInt(2, idDepartment);
			cs.setString(3, "%"+name+"%");
			cs.setString(4, code);
			rs = cs.executeQuery();
			if(keyword.equals("getEmployeeAttendance")){
				while (rs.next()) {
					pe_eplye emp = new pe_eplye();
					
					emp.setId(rs.getString("e.id"));
					emp.setFtne(rs.getString("e.ftne"));
					emp.setLtne(rs.getString("e.ltne"));
					
					listTemp.add(emp);
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return listTemp;
	}

	public static ArrayList<pe_eplye> getAllStaff(){
		ArrayList<pe_eplye> listTemp = new ArrayList<pe_eplye>();
		pe_eplye object = null;
		String query = "select * from pe_eplye e, pe_deptmt d where e.deptmt = d.id and e.teid in (1,2)"; // chinh thuc va thu viec
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				object = new pe_eplye();
				object.setSelect(false);
				object.setId(rs.getString("id"));
				object.setFtne(rs.getString("ftne"));
				object.setLtne(rs.getString("ltne"));
				object.setBhdy(rs.getTimestamp("bhdy"));
				object.setSx(rs.getInt("sx"));
				object.setEl(rs.getString("el"));
				object.setAdrs(rs.getString("adrs"));
				object.setPenmbr(rs.getString("penmbr"));
				object.setTeid(rs.getInt("teid"));
				object.setPe_deptmt(new pe_deptmt());
				object.getPe_deptmt().setId(rs.getInt("deptmt"));
				object.getPe_deptmt().setKywd(rs.getString("kywd"));
				object.setSatsid(rs.getInt("satsid"));
				object.setDscptn(rs.getString("dscptn"));
				listTemp.add(object);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return listTemp;
	}
	
	public static ArrayList<pe_eplye> getAllCandidate(){
		ArrayList<pe_eplye> listTemp = new ArrayList<pe_eplye>();
		pe_eplye object = null;
		String query = "select * from pe_eplye e where teid = 3"; // ung vien
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				object = new pe_eplye();
				object.setSelect(false);
				object.setId(rs.getString("id"));
				object.setFtne(rs.getString("ftne"));
				object.setLtne(rs.getString("ltne"));
				object.setBhdy(rs.getTimestamp("bhdy"));
				object.setSx(rs.getInt("sx"));
				object.setEl(rs.getString("el"));
				object.setAdrs(rs.getString("adrs"));
				object.setPenmbr(rs.getString("penmbr"));
				object.setTeid(rs.getInt("teid"));
				object.setPe_deptmt(new pe_deptmt());
				object.getPe_deptmt().setId(rs.getInt("deptmt"));
				object.setSatsid(rs.getInt("satsid"));
				object.setDscptn(rs.getString("dscptn"));
				listTemp.add(object);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return listTemp;
	}
	
	public static boolean addStaff(pe_eplye object1, pe_eplye_detl object2){
		int row = 0;
		try{
			String query = "call sp_pe_employee(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);
			cs =  conn.prepareCall(query);
			String id = "E" + Tool.generateID();
			//insert into employee
			cs.setString(1,"insert");
			
			cs.setString(2, id);cs.setString(3, object1.getFtne());
			cs.setString(4, object1.getLtne());cs.setString(5, Tool.format2.format(object1.getBhdy()));cs.setInt(6, object1.getSx());			
			cs.setString(7, object1.getEl());cs.setString(8, object1.getAdrs());cs.setString(9, object1.getPenmbr());
			cs.setInt(10, object1.getTeid());cs.setInt(11, object1.getPe_deptmt().getId());cs.setInt(12,1);
			cs.setString(13, object1.getDscptn());
			//insert into employee detail
			cs.setString(14, object2.getCde());cs.setString(15, object2.getRelign());cs.setString(16, object2.getNatnnlity());
			cs.setString(17, object2.getLgge());cs.setString(18, object2.getIycd());cs.setString(19, Tool.format2.format(object2.getIyde()));
			cs.setString(20, object2.getIype());cs.setDouble(21, object2.getBascsary());cs.setDouble(22, object2.getAllws());
			cs.setString(23, object2.getBk());cs.setString(24, object2.getBkat());cs.setString(25, Tool.format2.format(object2.getStde()));
			cs.setString(26, Tool.format2.format(object2.getStde()));cs.setString(27, object2.getPositn());
			row = cs.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static boolean addCandidate(pe_eplye object){
		int row = 0;
		try{
			String query = "call sp_pe_candidate(?,?,?,?,?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);
			cs =  conn.prepareCall(query);
			String id = "E" + Tool.generateID();
			//insert into employee
			cs.setString(1,"insert");
			
			cs.setString(2, id);cs.setString(3, object.getFtne());
			cs.setString(4, object.getLtne());cs.setString(5, Tool.format2.format(object.getBhdy()));cs.setInt(6, object.getSx());			
			cs.setString(7, object.getEl());cs.setString(8, object.getAdrs());cs.setString(9, object.getPenmbr());
			cs.setInt(10, 3);cs.setInt(11,1);
			cs.setString(12, object.getDscptn());
			row = cs.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static boolean updateStaff(pe_eplye object1, pe_eplye_detl object2){
		int row = 0;
		try{
			String query = "call sp_pe_employee(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);
			cs =  conn.prepareCall(query);
			//insert into employee
			cs.setString(1,"update");
			
			cs.setString(2, object1.getId());cs.setString(3, object1.getFtne());
			cs.setString(4, object1.getLtne());cs.setString(5, Tool.format2.format(object1.getBhdy()));cs.setInt(6, object1.getSx());			
			cs.setString(7, object1.getEl());cs.setString(8, object1.getAdrs());cs.setString(9, object1.getPenmbr());
			cs.setInt(10, object1.getTeid());cs.setInt(11, object1.getPe_deptmt().getId());cs.setInt(12,1);
			cs.setString(13, object1.getDscptn());
			//insert into employee detail
			cs.setString(14, object2.getCde());cs.setString(15, object2.getRelign());cs.setString(16, object2.getNatnnlity());
			cs.setString(17, object2.getLgge());cs.setString(18, object2.getIycd());cs.setString(19, Tool.format2.format(object2.getIyde()));
			cs.setString(20, object2.getIype());cs.setDouble(21, object2.getBascsary());cs.setDouble(22, object2.getAllws());
			cs.setString(23, object2.getBk());cs.setString(24, object2.getBkat());cs.setString(25, Tool.format2.format(object2.getStde()));
			cs.setString(26, Tool.format2.format(object2.getStde()));cs.setString(27, object2.getPositn());
			row = cs.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static boolean updateCandidate(pe_eplye object){
		int row = 0;
		try{
			String query = "call sp_pe_candidate(?,?,?,?,?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);
			cs =  conn.prepareCall(query);
			cs.setString(1,"update");
			
			cs.setString(2, object.getId());cs.setString(3, object.getFtne());
			cs.setString(4, object.getLtne());cs.setString(5, Tool.format2.format(object.getBhdy()));cs.setInt(6, object.getSx());			
			cs.setString(7, object.getEl());cs.setString(8, object.getAdrs());cs.setString(9, object.getPenmbr());
			cs.setInt(10, 3);cs.setInt(11,1);
			cs.setString(12, object.getDscptn());
			row = cs.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static pe_eplye_detl getDetailStaff(String id){
		pe_eplye_detl object = null;
		try{
			String query = "select *from pe_eplye_detl where id = ?";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			ps.setString(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				object = new pe_eplye_detl();
				object.setCde(rs.getString("cde"));
				object.setRelign(rs.getString("relign"));
				object.setNatnnlity(rs.getString("natnnlity"));
				object.setLgge(rs.getString("lgge"));
				object.setIycd(rs.getString("iycd"));
				object.setIyde(rs.getDate("iyde"));
				object.setIype(rs.getString("iype"));
				object.setBascsary(rs.getDouble("bascsary"));
				object.setAllws(rs.getDouble("allws"));
				object.setBk(rs.getString("bk"));
				object.setBkat(rs.getString("bkat"));
				object.setStde(rs.getDate("stde"));
				object.setEdde(rs.getDate("edde"));
				object.setPositn(rs.getString("positn"));
			}
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return object;
	}
}
